from flask import Flask, render_template, request, redirect, url_for
from flask_sqlalchemy import SQLAlchemy
import pymysql

# 实例化Flask应用，指定静态文件目录为'static'
app = Flask(__name__, static_folder='static')


db_config = {
    'host': 'localhost',
    'user': 'root',
    'password': '123456',
    'database': 'login'
}

# 创建表
def create_tables():
    """
    初始化数据库表：
    1. 创建users表，用于存储用户信息，包括id、username和password字段。
    2. 创建student_score表，用于存储学生信息，包括id、name和score字段。
    """
    connection = pymysql.connect(**db_config)
    cursor = connection.cursor()
    cursor.execute('''CREATE TABLE IF NOT EXISTS users (
                        id INT AUTO_INCREMENT PRIMARY KEY,
                        username VARCHAR(255) UNIQUE NOT NULL,
                        password VARCHAR(255) NOT NULL
                    )''')
    # 创建数据库表student_score，表结构为id,name,score
    cursor.execute('''CREATE TABLE IF NOT EXISTS student_score (
                        id INT AUTO_INCREMENT PRIMARY KEY,
                        name VARCHAR(255) NOT NULL,
                        score INT NOT NULL
                    )''')

    connection.commit()
    cursor.close()
    connection.close()


@app.route('/')
# 设置index.html为启动页
def index():
    return render_template('index.html')


#

# 实现登录功能
@app.route('/login', methods=['GET', 'POST'])
def login():
    """
    处理用户登录请求：
    1. 获取用户提交的用户名和密码。
    2. 查询数据库中是否存在匹配的用户。
    3. 如果用户存在，重定向到欢迎页面；否则，返回错误信息。
    """
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        connection = pymysql.connect(**db_config)
        cursor = connection.cursor()
        cursor.execute('SELECT * FROM users WHERE username=%s AND password=%s', (username, password))
        user = cursor.fetchone()
        cursor.close()
        connection.close()
        if user:
            return redirect(url_for('welcome'))
        else:
            return 'Invalid username or password'
    return render_template('function/login.html')


# 实现注册功能
@app.route('/register', methods=['GET', 'POST'])
def register():
    """
    处理用户注册请求：
    1. 获取用户提交的用户名和密码。
    2. 将用户信息插入到数据库的users表中。
    3. 重定向到登录页面。
    """
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        connection = pymysql.connect(**db_config)
        cursor = connection.cursor()
        cursor.execute('INSERT INTO users (username, password) VALUES (%s, %s)', (username, password))
        connection.commit()
        cursor.close()
        connection.close()
        return redirect(url_for('login'))
    return render_template('function/register.html')


# 添加学生：学号id,姓名和分数
@app.route('/add_student', methods=['GET', 'POST'])
def add_student():
    """
    添加学生信息：
    1. 获取用户提交的学生id、姓名和分数。
    2. 将学生信息插入到数据库的student_score表中。
    3. 重定向到欢迎页面。
    """
    if request.method == 'POST':
        id = request.form['id']
        name = request.form['name']
        score = request.form['score']
        connection = pymysql.connect(**db_config)
        cursor = connection.cursor()
        cursor.execute('INSERT INTO student_score (id,name, score) VALUES (%s,%s, %s)', (id, name, score))
        connection.commit()
        cursor.close()
        connection.close()
        return redirect(url_for('welcome'))
    return render_template('function/add_student.html')


# 查询学生信息
@app.route('/select_stu_score', methods=['GET', 'POST'])
def select_stu_score():
    """
    查询学生信息：
    1. 获取用户提交的学生id、姓名或分数。
    2. 查询数据库中匹配的学生信息。
    3. 如果找到学生，返回学生信息页面；否则，返回未找到学生的提示。
    """
    if request.method == 'POST':
        id = request.form['id']
        name = request.form['name']
        score = request.form['score']
        connection = pymysql.connect(**db_config)
        cursor = connection.cursor()
        cursor.execute('SELECT * FROM student_score WHERE id=%s OR name=%s OR score=%s', (id, name, score))
        student = cursor.fetchone()
        cursor.close()
        connection.close()
        if student:
            return render_template('function/select_stu_score.html', student=student)
        else:
            return '没有找到学生'
    return render_template('function/select_stu_score.html')


# 删除学生信息功能
@app.route('/delete_student', methods=['GET', 'POST'])
def delete_student():
    if request.method == 'POST':
        id = request.form['id']
        name = request.form['name']
        score = request.form['score']
        connection = pymysql.connect(**db_config)
        cursor = connection.cursor()
        cursor.execute('DELETE FROM student_score WHERE id=%s OR name=%s OR score=%s', (id, name, score))
        connection.commit()
        cursor.close()
        connection.close()
        return redirect(url_for('welcome'))
    return render_template('function/delete_student.html')
@app.route('/about_us')
def about_us():
    return render_template('function/about_us.html')
@app.route('/help')
def help():
    return render_template('function/help.html')
@app.route('/welcome')
def welcome():
    return render_template('function/example_1.html')

if __name__ == '__main__':
    create_tables()
    app.run(debug=True)
